## coding=utf-8

import xdrlib, sys
import xlrd
import json
import uuid

filePath = '/Users/tianqishu812/Desktop/anda/doctor_hailun.xlsx'

insertSqlPath = '/Users/tianqishu812/Desktop/anda/test.sql'


def open_excel(file='file.xls'):
    try:
        data = xlrd.open_workbook(file)
        return data
    except Exception, e:
        print str(e)


# 根据索引获取Excel表格中的数据   参数:file：Excel文件路径     colnameindex：表头列名所在行的所以  ，by_index：表的索引
def excel_table_byindex(file='file.xls', colnameindex=0, by_index=0):
    data = open_excel(file)
    table = data.sheets()[by_index]
    nrows = table.nrows  # 行数
    ncols = table.ncols  # 列数
    colnames = table.row_values(colnameindex)  # 某一行数据
    list = []
    for rownum in range(1, nrows):

        row = table.row_values(rownum)
        if row:
            app = {}
            for i in range(len(colnames)):
                app[colnames[i]] = row[i]
            list.append(app)
    return list


# 根据名称获取Excel表格中的数据   参数:file：Excel文件路径     colnameindex：表头列名所在行的所以  ，by_name：Sheet1名称
def excel_table_byname(file=filePath, colnameindex=0, by_name=u'医生信息'):
    data = open_excel(file)
    table = data.sheet_by_name(by_name)
    nrows = table.nrows  # 行数
    colnames = table.row_values(colnameindex)  # 某一行数据
    list = []
    for rownum in range(1, nrows):
        row = table.row_values(rownum)
        if row:
            app = {}
            for i in range(len(colnames)):
                app[colnames[i]] = row[i]
            list.append(app)
    return list


def createInsertSql(jsonvalue):
    # print jsonvalue

    id = uuid.uuid4()

    # name = str(jsonvalue['name'].decode("unicode_escape").encode("utf8"))
    dept_name = jsonvalue['dept_name']

    name = jsonvalue['name']
    ins_name = jsonvalue['institution_name']
    certi_no = str(jsonvalue['certi_no'])
    sexx = str(jsonvalue['sex'])
    sex = sexx.replace('.0', "")
    tt = str(jsonvalue['telephone'])
    telephone = tt.replace('.0', "")
    drcn_ = str(jsonvalue['drcn'])
    drcn = drcn_.replace('.0', "")

    title = jsonvalue['title']
    dqcn_ = str(jsonvalue['dqcn'])
    dqcn = dqcn_.replace('.0', "")
    insertSql = 'INSERT INTO health_professional_hl ' \
                'VALUES (' \
                '\'' \
                + str(id) + \
                '\',' \
                '\'' \
                + ins_name + \
                '\', ' \
                '\'' \
                + dept_name + \
                '\', ' \
                '\'' \
                + name + \
                '\', ' \
                '\'1\',' \
                '\'' \
                + certi_no + \
                '\',' \
                '\'' \
                + sex + \
                '\',' \
                '\'' \
                + telephone + \
                '\',' \
                '\'\',' \
                'NULL, ' \
                '\'N\', ' \
                '\'tianqishu812\', ' \
                '\'2017-7-5 00:00:00\', ' \
                '\'tianqishu812\', ' \
                '\'2017-7-5 00:00:00\', ' \
                '\'\',' \
                '\'231200\', ' \
                '\'' \
                + drcn + \
                '\',' \
                '\'' \
                + title + \
                '\',' \
                '\'\',' \
                '\'' \
                + dqcn + \
                '\',' \
                '\'\',' \
                '\'1\');\n'
    return insertSql


def main():
    tables = excel_table_byname()
    totalSql = ''
    for row in tables:
        encode_json = json.dumps(row)
        decode_json = json.loads(encode_json)
        reload(sys)
        sys.setdefaultencoding('utf8')
        totalSql = totalSql + createInsertSql(decode_json)

    totalSql = totalSql + 'commit'
    print totalSql
    #写入文件
    file_object = open(insertSqlPath,'w')
    file_object.write(totalSql)
    file_object.close()


if __name__ == "__main__":
    main()
